
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 12/21/2012 22:28:11
-- Generated from EDMX file: C:\Projetos\Questionnarie\Owl.Questionnarie.Entities\QuestionnarieModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [QUESTIONNARIES];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Question_QuestionnarieQuestion]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Questionnarie_Question] DROP CONSTRAINT [FK_Question_QuestionnarieQuestion];
GO
IF OBJECT_ID(N'[dbo].[FK_QuestionChoice]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Choice] DROP CONSTRAINT [FK_QuestionChoice];
GO
IF OBJECT_ID(N'[dbo].[FK_Questionnarie_QuestionnarieQuestion]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Questionnarie_Question] DROP CONSTRAINT [FK_Questionnarie_QuestionnarieQuestion];
GO
IF OBJECT_ID(N'[dbo].[FK_SubjectQuestionnaries]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Questionnarie] DROP CONSTRAINT [FK_SubjectQuestionnaries];
GO
IF OBJECT_ID(N'[dbo].[FK_SubjectQuestions]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Question] DROP CONSTRAINT [FK_SubjectQuestions];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Choice]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Choice];
GO
IF OBJECT_ID(N'[dbo].[Question]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Question];
GO
IF OBJECT_ID(N'[dbo].[Questionnarie]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Questionnarie];
GO
IF OBJECT_ID(N'[dbo].[Questionnarie_Question]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Questionnarie_Question];
GO
IF OBJECT_ID(N'[dbo].[Subject]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Subject];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Choice'
CREATE TABLE [dbo].[Choice] (
    [ChoiceId] uniqueidentifier  NOT NULL,
    [QuestionId] uniqueidentifier  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [Correct] nchar(1)  NOT NULL
);
GO

-- Creating table 'Question'
CREATE TABLE [dbo].[Question] (
    [QuestionId] uniqueidentifier  NOT NULL,
    [QuestionnarieId] uniqueidentifier  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [QuestionType] nvarchar(1)  NOT NULL,
    [SubjectId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Questionnarie'
CREATE TABLE [dbo].[Questionnarie] (
    [QuestionnarieId] uniqueidentifier  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [Avaiable] bit  NOT NULL,
    [SubjectId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Subject'
CREATE TABLE [dbo].[Subject] (
    [SubjectId] uniqueidentifier  NOT NULL,
    [Description] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'Questionnarie_Question'
CREATE TABLE [dbo].[Questionnarie_Question] (
    [Question_QuestionId] uniqueidentifier  NOT NULL,
    [Questionnarie_QuestionnarieId] uniqueidentifier  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ChoiceId] in table 'Choice'
ALTER TABLE [dbo].[Choice]
ADD CONSTRAINT [PK_Choice]
    PRIMARY KEY CLUSTERED ([ChoiceId] ASC);
GO

-- Creating primary key on [QuestionId] in table 'Question'
ALTER TABLE [dbo].[Question]
ADD CONSTRAINT [PK_Question]
    PRIMARY KEY CLUSTERED ([QuestionId] ASC);
GO

-- Creating primary key on [QuestionnarieId] in table 'Questionnarie'
ALTER TABLE [dbo].[Questionnarie]
ADD CONSTRAINT [PK_Questionnarie]
    PRIMARY KEY CLUSTERED ([QuestionnarieId] ASC);
GO

-- Creating primary key on [SubjectId] in table 'Subject'
ALTER TABLE [dbo].[Subject]
ADD CONSTRAINT [PK_Subject]
    PRIMARY KEY CLUSTERED ([SubjectId] ASC);
GO

-- Creating primary key on [Question_QuestionId], [Questionnarie_QuestionnarieId] in table 'Questionnarie_Question'
ALTER TABLE [dbo].[Questionnarie_Question]
ADD CONSTRAINT [PK_Questionnarie_Question]
    PRIMARY KEY NONCLUSTERED ([Question_QuestionId], [Questionnarie_QuestionnarieId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [QuestionId] in table 'Choice'
ALTER TABLE [dbo].[Choice]
ADD CONSTRAINT [FK_QuestionChoice]
    FOREIGN KEY ([QuestionId])
    REFERENCES [dbo].[Question]
        ([QuestionId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_QuestionChoice'
CREATE INDEX [IX_FK_QuestionChoice]
ON [dbo].[Choice]
    ([QuestionId]);
GO

-- Creating foreign key on [SubjectId] in table 'Question'
ALTER TABLE [dbo].[Question]
ADD CONSTRAINT [FK_SubjectQuestions]
    FOREIGN KEY ([SubjectId])
    REFERENCES [dbo].[Subject]
        ([SubjectId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SubjectQuestions'
CREATE INDEX [IX_FK_SubjectQuestions]
ON [dbo].[Question]
    ([SubjectId]);
GO

-- Creating foreign key on [SubjectId] in table 'Questionnarie'
ALTER TABLE [dbo].[Questionnarie]
ADD CONSTRAINT [FK_SubjectQuestionnaries]
    FOREIGN KEY ([SubjectId])
    REFERENCES [dbo].[Subject]
        ([SubjectId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SubjectQuestionnaries'
CREATE INDEX [IX_FK_SubjectQuestionnaries]
ON [dbo].[Questionnarie]
    ([SubjectId]);
GO

-- Creating foreign key on [Question_QuestionId] in table 'Questionnarie_Question'
ALTER TABLE [dbo].[Questionnarie_Question]
ADD CONSTRAINT [FK_Questionnarie_Question_Question]
    FOREIGN KEY ([Question_QuestionId])
    REFERENCES [dbo].[Question]
        ([QuestionId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Questionnarie_QuestionnarieId] in table 'Questionnarie_Question'
ALTER TABLE [dbo].[Questionnarie_Question]
ADD CONSTRAINT [FK_Questionnarie_Question_Questionnarie]
    FOREIGN KEY ([Questionnarie_QuestionnarieId])
    REFERENCES [dbo].[Questionnarie]
        ([QuestionnarieId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Questionnarie_Question_Questionnarie'
CREATE INDEX [IX_FK_Questionnarie_Question_Questionnarie]
ON [dbo].[Questionnarie_Question]
    ([Questionnarie_QuestionnarieId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------